﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.IO;

using System.Data.SQLite;//SQLite数据库驱动

namespace SQLitedo
{
    class Program
    {
        static string _dbSQLitePath = null;
        static SQLiteConnection _connSQLite = null;

        static void Main(string[] args)
        {
            CreateDatabase("D:\\sqldo.sqlite3");
            
            CreateTable();
            
            InsertData();
            
            ReadData();

            UpdateData();
            
            ReadData();

            DeleteData();

            ReadData();

            Console.WriteLine("程序结束，任意键退出");
            Console.ReadLine();
         
        }

        /// <summary>
        /// 创建数据库文件
        /// </summary>
        /// <param name="dbPath"></param>
        static void CreateDatabase(string dbPath)
        {
            if (File.Exists(dbPath))
            {
                File.Delete(dbPath);
            }

            _dbSQLitePath = dbPath;
            SQLiteConnection.CreateFile(_dbSQLitePath);

            Console.WriteLine("数据库创建成功");
        }

        /// <summary>
        /// 创建数据库表结构
        /// </summary>
        static void CreateTable()
        {
            _connSQLite = new SQLiteConnection("Data Source=" + _dbSQLitePath);
            //打开数据库
            _connSQLite.Open();

            SQLiteCommand cmd = _connSQLite.CreateCommand();
            cmd.CommandText = "CREATE TABLE students (id INT, name VARCHAR2, age INT )";
            cmd.ExecuteNonQuery();

            _connSQLite.Close();

            Console.WriteLine("表结构创建成功");
            
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        static void InsertData()
        {
            _connSQLite.Open();

            for (int i = 1; i <= 100; i++)
            {
                //构造数据
                SQLiteParameter[] values = new SQLiteParameter[]
                {
                    new SQLiteParameter("@id", i),
                    new SQLiteParameter("@name", "zhangsan_"+i.ToString()),
                    new SQLiteParameter("@age", i + 20)
                };

                //利用SQL，插入数据
                SQLiteCommand cmd = _connSQLite.CreateCommand();
                cmd.Parameters.AddRange(values);
                cmd.CommandText = "INSERT INTO students(id, name, age) VALUES(@id, @name, @age)";
                cmd.ExecuteNonQuery();
            }

            _connSQLite.Close();

            Console.WriteLine("数据插入成功");
            
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        static void UpdateData()
        {
            _connSQLite.Open();

            for (int i = 1; i <= 100; i++)
            {
                SQLiteCommand cmd = _connSQLite.CreateCommand();
                cmd.CommandText = "UPDATE students SET age=@age WHERE id=@id";
                SQLiteParameter[] values = new SQLiteParameter[]
                {
                    new SQLiteParameter("@age", i + 30),
                    new SQLiteParameter("@id", i)
                };
                cmd.Parameters.AddRange(values);
                cmd.ExecuteNonQuery();
            }

            _connSQLite.Close();

            Console.WriteLine("数据更新成功");
        }

        /// <summary>
        /// 读取数据
        /// </summary>
        static void ReadData()
        {
            _connSQLite.Open();

            SQLiteCommand cmd = _connSQLite.CreateCommand();
            cmd.CommandText = "SELECT * FROM students";

            SQLiteDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine("ID:{0}, name:{1}, age:{2}",
                    reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2));
            }
            reader.Close();

            _connSQLite.Close();

            Console.WriteLine("数据读取成功");
            
        }

        /// <summary>
        /// 清空数据（表结构存在）
        /// </summary>
        static void DeleteData()
        {
            _connSQLite.Open();

            SQLiteCommand cmd = _connSQLite.CreateCommand();
            cmd.CommandText = "DELETE FROM students";

            cmd.ExecuteNonQuery();

            _connSQLite.Close();

            Console.WriteLine("数据删除成功");
        }
    }
}
